package com.ibm.spatiotemp.server.framework.processor.ifx;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Iterator;
import java.util.LinkedList;

import org.apache.log4j.Logger;

import com.fasterxml.jackson.databind.JsonNode;
import com.ibm.json.java.JSONArray;
import com.ibm.json.java.JSONObject;
import com.ibm.spatiotemp.server.framework.request.Request;
import com.ibm.spatiotemp.tools.Constants;
import com.ibm.spatiotemp.tools.CoreMessages;
import com.ibm.spatiotemp.tools.RegionBBox;
import com.ibm.spatiotemp.tools.STException;
import com.ibm.spatiotemp.tools.STSPoint;
import com.ibm.spatiotemp.tools.STSPointObject;
import com.ibm.spatiotemp.utils.ConfigManager;

public class IfxPositionService {
	
	public final static Logger logger = Logger.getLogger(IfxPositionService.class);
	
	public JSONArray getPosition(Connection conn, Request request) throws SQLException, STException {
		String name = null;
		String value = null;
		JsonNode bodyNode = request.getJnode();
		JsonNode idNode = bodyNode.get("id");
		if (idNode != null) {
			name = "id";
			value = idNode.asText();
		} else if (bodyNode.get("key") != null) {
			name = "key";
			value = bodyNode.get("key").asText();
		}
		if (name == null) {
			return queryPositions_2(conn, request);
		} else {
			return queryPosition(conn, request, name, value);
		}
	}
	
	protected JSONArray queryPosition(Connection conn, Request request, String name, String value) throws SQLException, STException {
		Statement st = null;
		String id = null;
		String key = null;
		String sqlString = null;
		ResultSet resultSet = null;
		JSONArray jsonArray = null;
		JSONObject json = null;
		STSPointObject trajectory = null;	
		String bboxString = null;
		
		JsonNode bodyNode = request.getJnode();		
		st = conn.createStatement();
		
		// find the id and key		
		if (name.equalsIgnoreCase("id")) {	
			id = value;
			key = null;
		} else {
			sqlString = "select " + Constants.IDCOLUMN_NAME + ", " + Constants.KEYCOLUMN_NAME + " from " + Constants.TABLE_NAME + " where " + Constants.KEYCOLUMN_NAME + " = \'" + value + "\'";		
			resultSet = st.executeQuery(sqlString);	
			if (resultSet.next()) {
				id = resultSet.getString(1).trim();
				key = resultSet.getString(2);
				if (key != null) {
					key = key.trim();
				}
			} else {
				throw new STException(String.format(CoreMessages.STHS0013E, name, value));
			}
			resultSet.close();
		}
		
		String temp = bodyNode.get("time").asText();
		temp = temp.replaceFirst("T", " ");
		String timestamp = temp;
		String time = "\'" + temp + "\'";
				
		// check bbox
		JsonNode bboxNode = bodyNode.get("bbox");
		if (bboxNode != null) {
			RegionBBox bbox = new RegionBBox(bboxNode);
			bboxString = "st_within(sts_getposition(" + Constants.TSCOLUMN_NAME + ", " + time + "), " + bbox.getRegionString() + ")";			
			// check relation
			sqlString = "select " + bboxString + " from " + Constants.TABLE_NAME + " where " + Constants.IDCOLUMN_NAME + "=\'" + id + "\'";
//			logger.info("check bbox sql = " + sqlString);
			resultSet = st.executeQuery(sqlString);				
			if (resultSet.next()) {
				if (!resultSet.getBoolean(1)) {
					return null;
				}
			} else {
				return null;
			}
		}
		
		try {		
			trajectory = getPosition(st, timestamp, id);
			if (trajectory != null) {
				jsonArray = new JSONArray();
				json = new JSONObject();
				json.put("id", id);
				if (key != null) {
					json.put("key", key);
				}
				json.put("trajectory", trajectory.toJSONObject());
				jsonArray.add(json);
			}
			st.close();
			return jsonArray;
		} catch (SQLException e1) {
			throw e1;
		} finally {
			try {
				st.close();
			} catch (SQLException e1) {}
		}
	}
	
	protected JSONArray queryPositions(Connection conn, Request request) throws SQLException, STException {
		Statement st = null;
		String id = null;
		String key = null;
		String sqlString = null;
		ResultSet resultSet = null;
		JSONArray jsonArray = null;
		JSONObject json = null;
		STSPointObject trajectory = null;
		String format = null;
		LinkedList<String> idList = new LinkedList<String>();
		int number;
		
		st = conn.createStatement();
		
		try {
			JsonNode bodyNode = request.getJnode();		
			String temp = bodyNode.get("time").asText();
			temp = temp.replaceFirst("T", " ");
			String timestamp = temp;
			String time = "\'" + temp + "\'";
						
			// check bbox
			JsonNode bboxNode = bodyNode.get("bbox");
			if (bboxNode != null) {
				RegionBBox bbox = new RegionBBox(bboxNode);
				// find all objects: their position (timestamp) within bbox	
				format = "select * from table(%s(\'%s\', \'%s\', %s, %s, %f)::set(lvarchar not null))(modid)";
				sqlString = String.format(format, Constants.GET_LOCWITHIN_SET, Constants.TABLE_NAME, Constants.TSCOLUMN_NAME, time, bbox.getRegionString(), 0.0);
				logger.info("select modid=" + sqlString);
				try {
					resultSet = st.executeQuery(sqlString);	
					while (resultSet.next()) {
						temp = resultSet.getString(1).trim();
						if (temp != null) {
							idList.add(temp);
						}
					}
					resultSet.close();
				} catch (SQLException e) {
					if (e.getErrorCode() == -9976) {
						logger.info("there is no ids to return");
						st.close();
						return null;
					} else {
						throw e;
					}
				}
			} else {
				// find all objects: their positions (timestamp)		
				format = "select %s from %s;";
				sqlString = String.format(format, Constants.IDCOLUMN_NAME, Constants.TABLE_NAME);			
				resultSet = st.executeQuery(sqlString);	
				while (resultSet.next()) {
					temp = resultSet.getString(1).trim();
					if (temp != null) {
						idList.add(temp);
					}
				}
				resultSet.close();
			}
			logger.info("There are " + idList.size() + " moving objects to getPosition.");
			jsonArray = new JSONArray();
			// get trajectory one by one,  intersect with bbox
			Iterator<String> iter = idList.iterator();			
			while (iter.hasNext()) {
				id = iter.next();
//				// find the id and key		
//				sqlString = "select " + Constants.KEYCOLUMN_NAME + " from " + Constants.TABLE_NAME + " where " + Constants.IDCOLUMN_NAME + " = \'" + id + "\'";
//				resultSet = st.executeQuery(sqlString);	
//				if (resultSet.next()) {
//					key = resultSet.getString(1);
//					if (key != null) {
//						key = key.trim();
//					}
//				} else {
//					key = null;
//				}
//				resultSet.close();					
				trajectory = getPosition(st, timestamp, id);
				if (trajectory != null) {					
					json = new JSONObject();
					json.put("id", id);
					if (key != null) {
						json.put("key", key);
					}
					json.put("trajectory",  trajectory.toJSONObject());
					jsonArray.add(json);
				}				
			}
			st.close();
			number = jsonArray.size();
			logger.info("Returned " + number + " positions.");
			if (number > 0) {
				return jsonArray;
			} else {		 
				return null;
			}
		} catch (SQLException e1) {
			throw e1;
		} finally {
			try {
				st.close();
			} catch (SQLException e1) {}
		}
	}
	
	protected JSONArray queryPositions_2(Connection conn, Request request) throws SQLException, STException {
		Statement st = null;
		String id = null;
		String key = null;
		String sqlString = null;
		String sqlStringFull = null;
		ResultSet resultSet = null;
		JSONArray jsonArray = null;
		JSONObject json = null;
		String format = null;
		int number;
		long time1 = 0;
		long time2, usedtime;
		String pos = null;
		STSPoint point = null;
		STSPointObject	pointObject = null;
		long timeLong;
		
		st = conn.createStatement();
		
		try {
			JsonNode bodyNode = request.getJnode();		
			String temp = bodyNode.get("time").asText();
			temp = temp.replaceFirst("T", " ");
			String timestamp = temp;
			try {
				timeLong = Timestamp.valueOf(timestamp).getTime();
			} catch (IllegalArgumentException e) {
				throw new STException(String.format(CoreMessages.STHS0001E, "time", timestamp));
			}
			String time = "\'" + temp + "\'";
						
			// check bbox
			JsonNode bboxNode = bodyNode.get("bbox");
			if (bboxNode != null) {
				RegionBBox bbox = new RegionBBox(bboxNode);
				// find all objects: their position (timestamp) within bbox	
				format = "select * from table(%s(\'%s\', \'%s\', %s, %s, %f)::set(lvarchar not null))(id)";
				sqlString = String.format(format, Constants.GET_LOCWITHIN_SET, Constants.TABLE_NAME, Constants.TSCOLUMN_NAME, time, bbox.getRegionString(), 0.0);
			} else {
				// find all objects: their positions (timestamp)		
				format = "select %s from %s";
				sqlString = String.format(format, Constants.IDCOLUMN_NAME, Constants.TABLE_NAME);			
			}
			jsonArray = new JSONArray();
			format = "select %s, %s(%s,%s) from %s where %s in (%s)";
			sqlStringFull = String.format(format, Constants.IDCOLUMN_NAME, Constants.GET_POSITION, Constants.TSCOLUMN_NAME, time, Constants.TABLE_NAME, Constants.IDCOLUMN_NAME, sqlString);
			if (ConfigManager.DEBUG_MODE) {
				time1 = System.currentTimeMillis();
			}
			resultSet = st.executeQuery(sqlStringFull);
			while (resultSet.next()) {
				id = resultSet.getString(1);
				pos = resultSet.getString(2);
				if (pos != null) {
					point = new STSPoint(timeLong, pos);
					pointObject = new STSPointObject();
					pointObject.setSTSPoint(point);
					json = new JSONObject();
					json.put("id", id);
					if (key != null) {
						json.put("key", key);
					}
					json.put("trajectory",  pointObject.toJSONObject());
					jsonArray.add(json);
				}
			}
			resultSet.close();
			if (ConfigManager.DEBUG_MODE) {
				time2 = System.currentTimeMillis();
				usedtime = time2 - time1;
				logger.info("usedtime=" + usedtime + " to get all positions");
			}			
			number = jsonArray.size();
			logger.info("Returned " + number + " positions.");
			if (number > 0) {
				return jsonArray;
			} else {		 
				return null;
			}
		} catch (SQLException e1) {
//			throw e1;/*here collection may be null so, return this execption, in this case just set result to be empty []*/
			jsonArray = new JSONArray();
			return jsonArray;
		} finally {
			try {
				st.close();
			} catch (SQLException e1) {}
		}
	}
	
	
	protected STSPointObject getPosition(Statement st, String timestamp, String id) throws SQLException, STException {
		String sqlString = null;
		ResultSet resultSet = null;
		String pos = null;
		STSPoint point = null;
		STSPointObject	pointObject = null;
		long timeLong;
		String time;
		
		try {
			timeLong = Timestamp.valueOf(timestamp).getTime();
		} catch (IllegalArgumentException e) {
			throw new STException(String.format(CoreMessages.STHS0001E, "time", timestamp));
		}
		time = "\'" + timestamp + "\'";
		
		sqlString = "select " + Constants.GET_POSITION + "(" + Constants.TSCOLUMN_NAME + ", " + time + ") from "
				+ Constants.TABLE_NAME + " where " + Constants.IDCOLUMN_NAME + "=\'" + id +"\'";
//		logger.info("sql by getposition for time=" + sqlString);
		resultSet = st.executeQuery(sqlString);
		if (resultSet.next()) {
			pos = resultSet.getString(1);
			if (pos != null) {
				point = new STSPoint(timeLong, pos);
				pointObject = new STSPointObject();
				pointObject.setSTSPoint(point);
			}
		}
		resultSet.close();
		return pointObject;
	}

}
